Apache POI-SXSSFWorkbook到底有多快

16th鐵人賽

團隊抓挖幫嘎

2024-10-07 23:16:39

1384 瀏覽

分享至

BigGridDemo strategy

在Apache POI-簡介這一篇中有提到，相較於XSSFWorkbook將所有的Workbook、Sheet、Row、Cell等物件全部由記憶體來負荷，SXSSFWorkbook將龐大的資料分成小部分，使用了流式處理(Streaming)的方式來即時逐步處理小部分資料，其餘資料暫時存入硬碟，以這種「BigGridDemo」策略來提升寫入大量資料到Excel時的效率。
這一篇就來實測看看SXSSFWorkbook到底快多少，值不值得他犧牲一部份的功能(讀取、刪改文件、做圖表等等)。

測試內容與條件

為了比較XSSFWorkbook與SXSSFWorkbook的寫入(匯出)效率，我用下方的程式進行測試：

傳入參數為直行數與列數，XSSFWorkbook與SXSSFWorkbook最大列數為1,048,576列，測試1萬列、10萬列、100萬列三種資料筆數
SXSSFWorkbook由記憶體處理的指定列數都以預設的100列來測試(程式中仍帶入100)，要設定不同列數可以在建構子new SXSSFWorkbook()中的參數rowAccessWindowSize帶入不同的數值
由於SXSSFWorkbook優化的是將指定列數的資料由記憶體處理，直行數多寡並沒有差別，因此固定為10行(最大行數為16,384行 (從 A 到 XFD）)
cell的內容是隨意輸入的固定文字
exportXSSFExcel()與exportSXSSFExcel()方法的內容除了Workbook之外都相同
執行時間以單元測試的時間紀錄

public void exportXSSFExcel(int columnNumber, int rowNumber) {
    try (ByteArrayOutputStream bos = new ByteArrayOutputStream();
         Workbook xssfWorkbook = new XSSFWorkbook();) {
        // 建立excel sheet(參數為sheetname)
        Sheet sheet = xssfWorkbook.createSheet("測試XSSFWorkbook");

        // 建立列物件(參數為列數，從0開始)
        Row titleRow = sheet.createRow(0);
        // 建立此列的單元格物件
        Cell titleCell = titleRow.createCell(0);
        // 設定cell的內容
        titleCell.setCellValue("測試XSSFWorkbook");

        for (int i = 0; i < rowNumber; i++) {
            Row contentRow = sheet.createRow(i + 1);
            for (int j = 0; j < columnNumber; j++) {
                Cell contentCell = contentRow.createCell(j);
                contentCell.setCellValue("abcdefghijk");
            }
        }

        // 匯出Excel
        xssfWorkbook.write(bos);
    } catch (Exception e) {
        // 寫入失敗
        throw new RuntimeException(e);
    }
}

public void exportSXSSFExcel(int columnNumber, int rowNumber) {
    try (ByteArrayOutputStream bos = new ByteArrayOutputStream();
             Workbook xssfWorkbook = new SXSSFWorkbook();) {
             以下略...
    }
}

@Test
public void exportXSSFExcelTest() {
    workbookDemoFacade.exportXSSFExcel(10, 100000);
}

@Test
public void exportSXSSFExcelTest() {
    workbookDemoFacade.exportSXSSFExcel(10, 100000);
}

測試結果

我的電腦有24GB的記憶體：

條件一：1萬列資料

處理時間
XSSFWorkbook：3 sec 401 ms
SXSSFWorkbook：1 sec 50 ms

條件二：10萬列資料

處理時間
XSSFWorkbook：16 sec 195 ms
SXSSFWorkbook：2 sec 947 ms

條件三：100萬列資料

處理時間
XSSFWorkbook：8 mins以上
SXSSFWorkbook：19 sec 548 ms

可以發現在1萬筆或更少的列數時兩者差異不大，但是超過1萬的情況下，SXSSFWorkbook跟XSSFWorkbook的差距會越來越明顯，甚至在寫入100萬列資料的情況下，XSSFWorkbook執行超過8分鐘後還跑不出來，發生了OutOfMemoryError的情況。
比較下來，在數十萬筆資料的時候SXSSFWorkbook可以比XSSFWorkbook快好幾分鐘，確實效率高滿有感的，也可以參考其他工程師做得更詳細的實測：Solving Slow Excel Generation using Apache POI